package com.tham.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
import com.tham.dto.CalendarioDTO;
import utiles.AppProperties;
import utiles.ConexionPool;

public class CalendarioDAO {
	
	private static Logger log = Logger.getLogger(CalendarioDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	
	public  List<CalendarioDTO> getCalendarioByYearAndProf(String year, String idProf) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		    
		List<CalendarioDTO> listCalendario = new ArrayList<CalendarioDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_PROFESIONAL.getCalendarioByYearAndProf(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, year);
			stmt.setString(2, idProf);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				CalendarioDTO paramDTO = new CalendarioDTO();				
				paramDTO.setFecha(rs.getString(1));
				paramDTO.setTipo(rs.getString(2));
				paramDTO.setLetra(rs.getString(3));
				paramDTO.setDescripcion(rs.getString(4));
				listCalendario.add(paramDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CalendarioDAO.getCalendarioByYearAndProf SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//connDao.desconecta(conn);
			conn.close();
			conn = null;
		}
		return listCalendario;		
	}
	
	
	public  List<CalendarioDTO> getCalendarioByYear(String year) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
			    
		List<CalendarioDTO> listCalendario = new ArrayList<CalendarioDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_PROFESIONAL.getCalendarioByYear(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, year);		
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				CalendarioDTO paramDTO = new CalendarioDTO();				
				paramDTO.setFecha(rs.getString(1));
				paramDTO.setTipo(rs.getString(2));
				paramDTO.setLetra(rs.getString(3));
				paramDTO.setDescripcion(rs.getString(4));
				listCalendario.add(paramDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CalendarioDAO.getCalendarioByYear SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			conn.close();
			conn = null;
		}
		return listCalendario;
	}
	
	public  List<CalendarioDTO> getCalendarioHolter(String idProf) throws SQLException, IOException{
		Connection conn = ConexionPool.getConnectionPool();	
		    
		List<CalendarioDTO> listCalendario = new ArrayList<CalendarioDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_THO_PROFESIONAL.getCalendarioHolter(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idProf);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				CalendarioDTO paramDTO = new CalendarioDTO();				
				paramDTO.setFecha(rs.getString(1));
				paramDTO.setLetra(rs.getString(2));
				paramDTO.setDescripcion(rs.getString(3));
				listCalendario.add(paramDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CalendarioDAO.getCalendarioHolter SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//connDao.desconecta(conn);
			conn.close();
			conn = null;
		}
		return listCalendario;		
	}

}
